Azure Synapse SQL Pool Statistics

Verify your statistics:

SELECT sm.[name]                           AS[schema_name]
,       tb.[name]                           AS[table_name]
,       st.[name]                           AS[stats_name]
,       st.[filter_definition]              AS[stats_filter_definition]
,       st.[has_filter]                     AS[stats_is_filtered]
,       STATS_DATE(st.[object_id],st.[stats_id])
AS[stats_last_updated_date]
,       co.[name]                           AS[stats_column_name]
,       ty.[name]                           AS[column_type]
,       co.[max_length]                     AS[column_max_length]
,       co.[precision]                      AS[column_precision]
,       co.[scale]                          AS[column_scale]
,       co.[is_nullable]                    AS[column_is_nullable]
,       co.[collation_name]                 AS[column_collation_name]
,       QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS two_part_name
,       QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
AS three_part_name
FROM sys.objects AS ob
JOIN sys.stats AS st ON ob.[object_id]      = st.[object_id]
JOIN sys.stats_columns AS sc ON st.[stats_id]       = sc.[stats_id]
AND st.[object_id]      = sc.[object_id]
JOIN sys.columns AS co ON sc.[column_id]      = co.[column_id]
AND sc.[object_id]      = co.[object_id]
JOIN sys.types AS ty ON co.[user_type_id]   = ty.[user_type_id]
JOIN sys.tables AS tb ON co.[object_id]      = tb.[object_id]
JOIN sys.schemas AS sm ON tb.[schema_id]      = sm.[schema_id]

To confirm additional metadata for the statistics, we will then want to get the schema, table and statistics from the above and use the below DBCC command to confirm the rows and sampled rows, steps, update time etc.

DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)

Any columns in joins, sorts, aggregate functions, etc., we want to make sure that we have statistics on those columns.

I have seen good plans when statistics sample sizes are at least 30 percent or greater.

As an extra aid, we usually recommend having auto create statistics enabled.

You can check to confirm if it is created on your system with the below:

SELECT name, is_auto_create_stats_on
FROM sys.databases;

To enable the setting you can invoke the following:

ALTER DATABASE dbnamehere
SET AUTO_CREATE_STATISTICS ON